<?php 
/*
	@author   ducminh_ajax <ducminh_ajax@yahoo.com.vn>
	@date 05/11/2011
	@update:
*/
class CrmOpportunityController extends Zone_Action{
	public function field(){
		$data = array(
			date_updated =>array(
				type			=> 'NONE',
				default_value   => new Model_Expr('NOW()')
			),
			updated_by_id =>array(
				type			=> 'NONE',
				default_value 	=> get_user_id()
			),
						
			code 	=> array(
				type 		=> 'CHAR',
				no_empty 	=>	true,
				form_label	=> 'Mã',			
			),
			title 	=> array(
				type 			=> 'CHAR',
				no_empty 		=> true,
				form_label		=> 'Tên'
			),
			probability 	=> array(
				type 			=> 'INT',
				form_label		=> 'Xác suất',
				max				=> 100,
				min				=> 0
			),
			expect 	=> array(
				type 			=> 'FLOAT',
				form_label		=> 'Kỳ vọng',
				min				=> 0
			),
			date_start 	=> array(
				type 			=> 'DATE',
				form_label		=> 'Ngày bắt đầu',
				fix_value		=> change_date_format
			),
			date_end 	=> array(
				type 			=> 'DATE',
				form_label		=> 'Ngày kết thúc',
				fix_value		=> change_date_format
			),
			
			desc 	=> array(
				type 			=> 'TEXT',
				form_label		=> 'Mô tả'
			),
			parent_id 	=> array(
				type 			=> 'INT',
				form_label		=> 'Giai đoạn bán hàng'
			),
			customer_id 	=> array(
				type 			=> 'INT',
				form_label		=> 'Công ty'
			),
			contact_id 	=> array(
				type 			=> 'INT',
				form_label		=> 'Liên hệ'
			),
			currency_id 	=> array(
				type 			=> 'INT',
				form_label		=> 'Tiền tệ'
			),
			care_by_id 	=> array(
				type 			=> 'INT',
				form_label		=> 'Chăm sóc'
			)		
		);
		return $data;	
	}
	
	public function indexAction(){
		$user_id = get_user_id();
		$vars  = array();
		
		$order_by = " ORDER BY `ID` DESC";
		
		$current_page = max(getInt('p',1),1);		
		$range_page = max(10,(int)$this->getConfig('user.page',20));
		if( isset($_REQUEST['p']))	$vars['p'] = $current_page;
		
		$limit = "LIMIT ".($current_page-1)*$range_page.",$range_page";
				
		$field_orders = 
			array(
				'`a`.`ID`'					=>	'ID',
				'`a`.`code`' 				=>	'code',
				'`a`.`title`'				=>	'title',
				'`a`.`desc`'				=>	'desc',
				'`a`.`expect`'				=>	'expect',
				'`a`.`probability`'			=>	'probability',
				'`a`.`customer_id`'			=> 	'customer_id',
				'`a`.`contact_id`'			=> 	'contact_id',
				'`a`.`parent_id`'			=>	'parent_id'
			);
			
		$field_groups = 
			array(
				'`a`.`parent_id`'			=>	'parent_id',
				'`a`.`customer_id`'			=> 	'customer_id',
				'`a`.`contact_id`'			=> 	'contact_id'
			);
			
		$field_texts = 
			array(
				'`a`.`code`' 				=>	'code',
				'`a`.`title`'				=>	'title',
				'`a`.`desc`'				=>	'desc',
				'`a`.`expect`'				=>	'expect',
				'`a`.`probability`'			=>	'probability'
			);			

		foreach( $field_orders as $f => $r ){
			if( get('order_by') == $r ){
				$order_type = ( get('order_type') == 'desc' ? 'desc' : 'asc');
				$order_by = "ORDER BY $f $order_type";
				$vars['order_by'] = $r;
				$vars['order_type'] = $order_type;
				break;	
			}	
		}
		
		$where = array("`a`.`is_draft`='no'");
		
		//more
		if( !empty($_REQUEST['date_start']) ){
			$date = get('date_start');
			$date_sql = change_date_format( $date );
			$where[] = "`a`.`date_start` >= '$date_sql'";
			$vars['date_start'] = $date;
		}

		if( !empty($_REQUEST['date_end']) ){
			$date = get('date_end');
			$date_sql = change_date_format( $date );
			$where[] = "`a`.`date_end` <= '$date_sql'";
			$vars['date_end'] = $date;
		}		
		
		$word = get('s','');
		if( $word != '' ){
			$a = array();
			foreach( $field_texts as $k => $f ){
				$a[] = app_search_sql_text($k, $word);
			}			
			$vars['s'] = $_REQUEST['s'];
			$where[]   = '('.implode(' OR ',$a).')';			
		}

		
		foreach( $field_texts as $k => $f ){
			$word = get($f,'');
			if( $word == '' ) continue;
			$where[] = app_search_sql_text($k, $word);
			$vars[$f] = $_REQUEST[$f];
		}			

		foreach( $field_groups as $k => $f ){
			if( is_array($_REQUEST[$f]) ){
				$s = implode(',',get($f,array(),true));
				$vr = implode(',', $_REQUEST[$f]);
			}else{
				$s = get($f,'');
				$vr = $_REQUEST[$f];
			}	
			if( $s != '' ){
				$where[]= app_search_sql_group( $k, $s);
				$vars[$f] = $vr;
			}	
		}		
		
		$where = implode(' AND ',$where);

		$a = $vars;
		unset($a['p']);
		if( $this->getAction() == 'Search' ){
			return $a;
		}
				
		$total = $this->Model->fetchOne("SELECT COUNT(*) 
			FROM `crm_opportunities` as `a` 
			WHERE $where");
		
		$posts = $this->Model->fetchAll("SELECT 
			`a`.*,
			`b`.`username` as `care_by_name`,
			`b`.`is_deleted` as `care_is_deleted`,
			`c`.`name` as `customer_name`,
			`r`.`title` as `currency_title`,
			`r`.`symbol` as `currency_symbol`								
			
			FROM `crm_opportunities` as `a` 
				LEFT JOIN `users` as `b`
					ON `a`.`care_by_id` = `b`.`ID`
				LEFT JOIN `crm_customers` as `c`
					ON `a`.`customer_id` = `c`.`ID`
				LEFT JOIN `currencies` as `r`
					ON `a`.`currency_id` = `r`.`ID`
				
			WHERE $where $order_by $limit");
					
		$a = $vars;
		unset($a['p']);
		
		$page = page_ajax('#Crm/Opportunity?'.to_query_configs($a,false).'&p=', $total , $range_page, $current_page);	
		$cats = $this->Model->fetchAll("SELECT * FROM `crm_opportunity_types` ORDER BY `title`");			
		
		$this->set(array(
			user_id =>get_user_id(),
			page	=> $page,
			total	=> $total,
			vars	=> $vars,
			posts	=> $posts,
			cats	=> $cats
		));
	}

	public function uploadAction(){
		$this->removeLayout();
		$post_id = getInt('post_id',0);
		$user_id = get_user_id();
		
		$post = $this
			->Model
			->fetchRow("SELECT * FROM `crm_opportunities` WHERE `ID`='$post_id'");
		
		if(!$post){
			return $this->setContent(error('Bản ghi đã bị xóa hoặc không tồn tại'));
		}	

		if( $post['created_by_id'] != $user_id && !$this->hasRole('crm_admin') 
				&& !( $post['care_by_id'] == $user_id && $post['is_draft']=='no' ) ){
			return $this->setContent(error('Bạn không có quyền sửa bản ghi này'));			
		}						
		
		if( isPost() ){
			loadClass('ZData');
			$f = new ZData();
			$f->addField(array(
				file => array(
					type 		=> 'FILE',
					path		=> 'files/crm/',
					no_empty	=> true
				)
			));
			
			$data = $f->getData();
			if( !is_array( $data ) ){
				return $this->setJSON(array(
					alert => error($data)
				));				
			}
			
			$file = $data['file'];
			$this->Model->insert('crm_files',array(
				post_id				=> $post_id,
				name				=> $file[0],
				type				=> $file[1],
				size				=> $file[2],
				filename			=> $file[3],
				mode				=> 'opportunity'		
			));
			
			$file_id = $this->Model->lastId();				
			return $this->setJSON(array(
				content	=> "<div class='x-file-info'>
					<input checked type='checkbox' name='files[]' value='$file_id'/>
					<a href='".ctrUrl($this)."/File?ID=$file_id' target='_blank'>{$file[3]} {$file[2]} KB	</a>
				</div>"
			));
		}
	}

	private function getOptions(){
		$cats = $this->Model->fetchAll("SELECT * FROM `crm_opportunity_types` ORDER BY `title`");
		$currencies = $this->Model->fetchAll("SELECT * FROM `currencies` ORDER BY `title` DESC");
		$this->set(array(
			cats		=> $cats,
			currencies  => $currencies
		));
	}	
	
	public function validData( $data ){
		if( !is_array( $data ) )
			return $data;
		return $data;
	}
	
	public function addAction(){
		$user_id = get_user_id();	
	
		if( !isPost() ){
			$post = $this->Model->fetchRow("SELECT * 
				FROM `crm_opportunities` 
				WHERE 
					`created_by_id`='$user_id' 
					AND `is_draft`='yes'");
			
			$post_id = $post['ID'];			
			
			if( !$post ){
				$this->Model->insert('crm_opportunities',array(
					created_by_id	=>	get_user_id(),
					date_created 	=> 	new Model_Expr('NOW()')
				));
				
				$post_id = $this->Model->lastId();				
			}
			
			$this->getOptions();			
			
			$this->set(array(
				ID	=> $post_id
			));		
		
		}else{		
		
			$post_id = getInt('ID',0);
			if( !$this->Model->fetchRow("SELECT * FROM `crm_opportunities` WHERE `created_by_id`='$user_id' AND `ID`='$post_id'") ){
				$this->setJSON(array(
					message	=> error('Bản ghi đã bị xóa hoặc không tồn tại')
				));
			}
		
			loadClass('ZData');
			$f = new ZData();
			$f->addFields( $this->field());
			
			$data = $f->getData();
			$data = $this->validData( $data );
			
			if( !is_array($data)){
				return $this->setJSON(array(
					message => error($data)
				));
			}		

			$data['date_created']  = new Model_Expr('NOW()');
			$data['is_draft']='no';
			
			$this->Model->update('crm_opportunities',$data,"`ID`='$post_id'");
			
			//file hadler
			$files_selected = get('files',array(),true);
			$files = $this->Model->fetchAll("SELECT * FROM `crm_files` 
				WHERE `post_id`='$post_id' AND `mode`='opportunity'");
			foreach( $files as $f ){
				if( !in_array( $f['ID'], $files_selected ) ){
					$this->Model->delete('crm_files',"`ID`='{$f['ID']}'");
					@unlink("files/crm/{$f['name']}");
				}
			}			
				
			return $this->setJSON(array(
				redirect	=> "#Crm/Opportunity/View?ID=$post_id"
			));
		}
	}

	public function editAction(){
		$post_id = getInt('ID',0);
		$user_id = get_user_id();
				
		$post = $this
			->Model
			->fetchRow("SELECT * FROM `crm_opportunities` WHERE `ID`='$post_id' AND `is_draft`='no'");
		
		if(!$post){
			return $this->setContent(error('Bản ghi đã bị xóa hoặc không tồn tại'));
		}	

		if( $post['created_by_id'] != $user_id AND !$this->hasRole('crm_admin') AND $post['care_by_id'] != $user_id){
			return $this->setContent(error('Bạn không có quyền sửa bản ghi này'));			
		}				
		
		if( !isPost() ){	
			
			$this->getOptions();			
			$this->set(array(
				ID	 => $post_id,
				post => $post
			));
			
			$files = $this->Model->fetchAll("SELECT * FROM `crm_files` WHERE `mode`='opportunity' AND `post_id`='$post_id'");
			$this->set(array(
				files => $files
			));
			
			$care = $this->Model->fetchRow("SELECT `username`,`ID`,`is_deleted` 
				FROM `users` WHERE `ID`='{$post['care_by_id']}'");
			
			if( $care ){
				$this->set(array(
					care => $care
				));
			}

			$customer = $this->Model->fetchRow("SELECT `name`,`ID`
				FROM `crm_customers` WHERE `ID`='{$post['customer_id']}' AND `is_draft`='no'");
			
			$contact = $this->Model->fetchRow("SELECT `title`,`ID`
				FROM `crm_contacts` WHERE `ID`='{$post['contact_id']}' AND `is_draft`='no'");
			
			$this->set(array(
				contact 	=> $contact,
				customer	=> $customer	
			));
		}else{
			loadClass('ZData');
			
			$f = new ZData();
			$f->addFields( $this->field());

			$data = $f->getData();
			$data = $this->validData( $data );
			
			if( !is_array($data)){
				return $this->setJSON(array(
					message => error($data)
				));
			}		

			$data['date_created']  = new Model_Expr('NOW()');
			$data['is_draft']='no';
			
			$this->Model->update('crm_opportunities',$data,"`ID`='$post_id'");
			
			//file hadler
			$files_selected = get('files',array(),true);
			
			$files = $this->Model->fetchAll("SELECT * FROM `crm_files` 
				WHERE `post_id`='$post_id' AND `mode`='opportunity'");
				
			foreach( $files as $f ){
				if( !in_array( $f['ID'], $files_selected ) ){
					$this->Model->delete('crm_files',"`ID`='{$f['ID']}'");
					@unlink("files/crm/{$f['name']}");
				}
			}			
				
			return $this->setJSON(array(
				redirect => "#Crm/Opportunity/View?ID=$post_id"
			));
		}
	}

	public function viewAction(){
		$post_id = getInt('ID',0);
		
		$post  = $this->Model->fetchRow("
			SELECT `a`.*,
					`b`.`username` as `created_by_name`,
					`b`.`is_deleted` as `created_is_deleted`,
					`c`.`username` as `updated_by_name`,
					`c`.`is_deleted` as `updated_is_deleted`,
					`m`.`username` as `care_by_name`,
					`m`.`is_deleted` as `care_is_deleted`,
					`e`.`title` as `parent_title`,
					`f`.`name` as `customer_name`,
					`h`.`title` as `contact_title`,
					`r`.`title` as `currency_title`,
					`r`.`symbol` as `currency_symbol`
			FROM `crm_opportunities` as `a`
				LEFT JOIN `users` as `b` 
					ON `b`.`ID`=`a`.`created_by_id`
				LEFT JOIN `users` as `c` 
					ON `c`.`ID`=`a`.`updated_by_id`
				LEFT JOIN `users` as `m` 
					ON `m`.`ID`=`a`.`care_by_id`
				LEFT JOIN `crm_opportunity_types` as `e` 
					ON `e`.`ID`=`a`.`parent_id`
				LEFT JOIN `crm_customers` as `f` 
					ON `f`.`ID`=`a`.`customer_id`
				LEFT JOIN `crm_contacts` as `h` 
					ON `h`.`ID`=`a`.`contact_id`
				LEFT JOIN `currencies` as `r`
					ON `a`.`currency_id` = `r`.`ID`
					
			WHERE `a`.`ID`='$post_id' AND `a`.`is_draft`='no'");
			
		if(!$post){
			return $this->setContent(error('Bản ghi đã bị xóa hoặc không tồn tại'));
		}
		
		$files = $this->Model->fetchAll("SELECT * FROM `crm_files` 
			WHERE `mode`='opportunity' AND `post_id`='$post_id'");

		$care = $this->Model->fetchRow("SELECT `username`,`ID`,`is_deleted` 
			FROM `users` WHERE `ID`='{$post['care_by_id']}'");
		
		if( $care ){
			$this->set(array(
				care => $care
			));
		}
	
		$this->set(array(
            post =>	$post,
			files => $files			
        ));
	}	

	public function deleteAction(){
		$ids = get('ID',array(),true);
		$user_id = get_user_id();
		
		foreach( $ids as $post_id ){
			$post = $this->Model->fetchRow("SELECT * FROM `crm_opportunities` WHERE `ID`='$post_id'");

			if( !$post  ){
				continue;
			}
			
			if( $post['created_by_id'] != $user_id AND !$this->hasRole('crm_admin') ){
				continue;
			}
			
			$this->Model->delete('crm_opportunities'," `ID`='$post_id' ");
			
			$files = $this->Model->fetchAll("SELECT * FROM `crm_files` WHERE `post_id`='$post_id' AND `mode`='opportunity'");
			$this->Model->delete('crm_files',"`post_id`='$post_id' AND `mode`='opportunity'");
					
			foreach( $files as $file ){
				@unlink("files/crm/{$file['name']}");
			}

		}
		
		return $this->setJSON(array(
			redirect => '#'.get('url','Crm/Opportunity')
		));
	}
	
	public function fileAction(){
		$file_id = get('ID',0);
		$user_id = get_user_id();
		$file = $this
			->Model->fetchRow("SELECT * FROM `crm_files` as `a` 
				WHERE `a`.`ID`='$file_id' AND `mode`='opportunity' LIMIT 1");

		if(!$file){
			return $this->setContent(error('File đã bị xóa hoặc không tồn tại'));
		}
		$post  = $this->Model
			->fetchRow("SELECT * FROM `crm_opportunities`
				WHERE `ID`='{$file['post_id']}' AND `is_draft`='no'");
			
		if(!$post){
			return $this->setContent(error('File đã bị xóa hoặc không tồn tại'));
		}		
		
		if( $post['created_by_id'] != $user_id AND !$this->hasRole('crm_admin') AND $post['care_by_id'] != $user_id){
			return $this->setContent(error('Bạn không có quyền xem file này'));			
		}				
		
		$file_path = "files/crm/{$file['name']}";
		
		if( !file_exists( $file_path ) ){
			return $this->setError('404');
		}	
		
		if( get('type') == 'icon' ){	
			$img_types = array('image/gif','image/png','image/x-png','image/jpeg','image/x-jpeg','image/jpg');
			if( in_array($file['type'],$img_types) ){
				loadClass('PHPThumb');
				$thumb = PhpThumbFactory::create($file_path);
				$thumb->resize(60, 60);
				$thumb->show();
			}else{
				@readfile("files/static/icon.gif");	
			}		
		}else if( get('type') == 'view' ){	
			$img_types = array('image/gif','image/png','image/x-png','image/jpeg','image/x-jpeg','image/jpg');
			if( in_array($file['type'],$img_types) ){
				loadClass('PHPThumb');
				$thumb = PhpThumbFactory::create($file_path);
				$thumb->show();
			}else{
				@readfile("files/static/icon.gif");	
			}		
		}else{
			@header("Content-Disposition: attachment; filename={$file['name']}");
			@readfile($file_path);
		}
	}	

	public function exportAction(){
		$this->removeLayout();
		$this->setView('_FormExport');
		$fields = array(
			ID				=> array('ID',true),
			code			=> array('Mã cơ hội',true),
			title			=> array('Tên cơ hội',true),
			customer_name	=> array('Công ty'),		
			contact_title	=> array('Liên hệ'),		
			date_start		=> array('Ngày bắt đầu'),
			date_end		=> array('Ngày kết thúc'),
			care_by_name	=> array('Người chăm sóc'),
			expect			=> array('Kỳ vọng'),
			probability		=> array('Xác suất'),
			expect			=> array('Kỳ vọng'),
			parent_title	=> array('Giai đoạn bán hàng'),
			desc			=> array('Mô tả')
		);
			
		if( !isPost() ){	
			$this->set(array(
				fields	=> $fields
			));		
		
		}else{
			$ids = get('ID',array(),true);
		
			if( count($ids) == 0){
				die("<script>
					window.parent.$.Alert('Chưa có bản ghi nào được chọn');
				</script>");
			}			

			$ids = join(",",$ids);	
			
			$posts = $this->Model->fetchAll("
				SELECT `a`.*,
					`e`.`name` as `customer_name`, 
					`f`.`title` as `contact_title`, 
					IF(IFNULL(`a`.`date_start`,'0000-00-00')<>'0000-00-00',DATE_FORMAT(`a`.`date_start`,'%d/%m/%Y'),'') as `date_start`,
					IF(IFNULL(`a`.`date_end`,'0000-00-00')<>'0000-00-00',DATE_FORMAT(`a`.`date_end`,'%d/%m/%Y'),'') as `date_end`,
					`t`.`title` as `parent_title`,
					CONCAT( FORMAT(`a`.`expect`,0),`c`.`symbol`) as `expect`						
				FROM `crm_opportunities` as `a`
					LEFT JOIN `crm_customers` as `e` 
						ON `e`.`ID`=`a`.`customer_id`
					LEFT JOIN `crm_contacts` as `f` 
						ON `f`.`ID`=`a`.`contact_id`
					LEFT JOIN `crm_opportunity_types` as `t` 
						ON `t`.`ID`=`a`.`parent_id`
					LEFT JOIN `currencies` as `c` 
						ON `c`.`ID`=`a`.`currency_id`
					WHERE
						`a`.`ID` IN ($ids) AND `a`.`is_draft`='no'
			");	

			$type = get('filetype','xml');		
			loadClass('ZExport');
			
			$export = new ZExport();
			$columns = get('fields',array(),true);

			$export->set($fields, $columns, $posts, $type, get('order') );
			$export->setTitle('Danh sách liên hệ');
			$export->setFile('Office_Export_Crm_Opportunity');
			$export->export();			
		}	
	}		

	public function searchAction(){
		if( !isPost() ){
			$this->getOptions();
		}else{
			$vars = $this->indexAction();
			if( count($vars) == 0 ){
				return $this->setJSON(array(
					alert	=> 'Chưa có thông số tìm kiếm'
				));
			}else{
				return $this->setJSON(array(
					redirect	=> '#Crm/Contact?'.to_query_configs($vars,false)
				));
			}
		}
	}	

	
}
?>